package com.starbaba.creditcard.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.base.common.log.LogService;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.creditcard.model.CreditCardUserInfo;
import com.starbaba.stat.model.LoanBackCore;
import com.starbaba.stat.utils.Constant;
import com.tuanzi.server.loan_base.model.db.credit.PayOrder;
import com.tuanzi.server.loan_base.util.PhoneNumNideUtil;

@SuppressWarnings("rawtypes")
@Component
public class CreditCardUserInfoService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);  //正式stat
	private BaseDao daoLoan = new BaseDao("tt1");  ///数据库loan
	
	public void showPage(Map<String, Object> paramsMap) throws Exception {  
		List<CreditCardUserInfo> channelList = daoLoan.list("SELECT c.channel_code channelId,c.channel_name channelName from comm_product_channel c", CreditCardUserInfo.class);
		List<CreditCardUserInfo> prdList = daoLoan.list("SELECT p.prdid prodId,p.name prodName from comm_product_prdid p", CreditCardUserInfo.class);
		List<CreditCardUserInfo> bankList = daoLoan.list("SELECT b.bank_name creditCardBank FROM bank_list b", CreditCardUserInfo.class);
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
		paramsMap.put("bankList", bankList);
	}
	
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		String mobile = (String) paramsMap.get("mobile");
		String credit_user_name = (String) paramsMap.get("credit_user_name"); 
		String credit_card_bank = (String) paramsMap.get("credit_card_bank"); 
		String credit_card_num = (String) paramsMap.get("credit_card_num");
		String credit_limit = (String) paramsMap.get("credit_limit"); 
		String statement_date = (String) paramsMap.get("statement_date"); 
		String if_notify = (String) paramsMap.get("if_notify"); 
		String regiStartTime = (String) paramsMap.get("regiStartTime"); 
		String regiEndTime = (String) paramsMap.get("regiEndTime"); 
		String addStartTime = (String) paramsMap.get("addStartTime"); 
		String addEndTime = (String) paramsMap.get("addEndTime"); 
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(platform)) {
			whereParams.append("AND c.platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id)) {
			whereParams.append("AND c.prdid = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"".equals(channel)) {
			whereParams.append("AND c.channel = '" + channel + "' ");
		}
		if (StringUtils.isNotEmpty(mobile) && !"".equals(mobile)) {
			whereParams.append("AND u.mobnum LIKE '%" + mobile + "%' ");
		}
		if (StringUtils.isNotEmpty(credit_user_name) && !"".equals(credit_user_name)) {
			whereParams.append("AND c.credit_user_name LIKE '%" + credit_user_name + "%' ");
		}
		if (StringUtils.isNotEmpty(credit_card_bank) && !"".equals(credit_card_bank)) {
			whereParams.append("AND c.credit_card_bank = '" + credit_card_bank + "' ");
		}
		if (StringUtils.isNotEmpty(credit_card_num) && !"".equals(credit_card_num)) {
			whereParams.append("AND c.credit_card_num LIKE '%" + credit_card_num + "%' ");
		}
		if (StringUtils.isNotEmpty(credit_limit) && !"".equals(credit_limit)) {
			whereParams.append("AND c.credit_limit = " + credit_limit + " ");
		}
		if (StringUtils.isNotEmpty(statement_date) && !"".equals(statement_date)) {
			whereParams.append("AND c.statement_date = " + statement_date + " ");
		}
		if (StringUtils.isNotEmpty(if_notify) && !"".equals(if_notify)) {
			whereParams.append("AND c.if_notify = " + if_notify + " ");
		}
		if (StringUtils.isNotEmpty(regiStartTime) && !"".equals(regiStartTime)) {
			whereParams.append("AND u.createtime >= '" + regiStartTime + " 00:00:00' ");
		}
		if (StringUtils.isNotEmpty(regiEndTime) && !"".equals(regiEndTime)) {
			whereParams.append("AND u.createtime <= '" + regiEndTime + " 23:59:59' ");
		}
		if (StringUtils.isNotEmpty(addStartTime) && !"".equals(addStartTime)) {
			whereParams.append("AND c.ctime >=  '" + addStartTime + " 00:00:00' ");
		}
		if (StringUtils.isNotEmpty(addEndTime) && !"".equals(addEndTime)) {
			whereParams.append("AND c.ctime <= '" + addEndTime + " 23:59:59' ");
		}
		
		
		StringBuilder listSql = new StringBuilder("SELECT c.platform platform,p.name prodName,ch.channel_name channelName,u.createtime regiTime,"
				+ "u.lasttime lastTime,c.ctime createTime,u.id userid,u.mobnum mobile,c.credit_user_name creditUserName,c.credit_card_bank creditCardBank,"
				+ "c.credit_card_num creditCardNum,c.credit_limit creditLimit,c.statement_date statementDate,c.if_notify ifNotify "
				+ "FROM loan_credit_card_binding_info c  LEFT OUTER JOIN comm_user u ON c.userid = u.id  LEFT OUTER JOIN comm_product_channel ch ON c.channel = ch.channel_code "
				+ "LEFT OUTER JOIN comm_product_prdid p ON c.prdid = p.prdid WHERE 1 = 1 ");
		listSql.append(whereParams);
		listSql.append("order by c.ctime desc ");
		
		String sqlCount = "select count(1) from (" + listSql.toString() + ") z ";
		if (start != null && length != null) {
			listSql.append("limit " + start + ", " + length);
		}
		int count = daoLoan.getInt(sqlCount);
		
//		String prodSql = "SELECT p.prdid appProdId,p.name prodName from comm_product_prdid p";
//		List<CreditCardUserInfo> prodList = daoLoan.list(prodSql,CreditCardUserInfo.class);
//		
//		String channelSql = "SELECT c.channel_code channelId,c.channel_name channel from comm_product_channel c";
//		List<CreditCardUserInfo> channelList = daoLoan.list(channelSql,CreditCardUserInfo.class);
		
		List<CreditCardUserInfo> list = daoLoan.list(listSql.toString(), CreditCardUserInfo.class);
		for (CreditCardUserInfo model : list) {
			if (model.getPlatform() == null || "".equals(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (model.getProdName() == null || "".equals(model.getProdName())) {
				model.setProdName("未知");
			}
			if (model.getChannelName() == null || "".equals(model.getChannelName())) {
				model.setChannelName("未知");
			}
			if (model.getIfNotify() == 0 ) {
				model.setIfNotifyStr("否");
			}else{
				model.setIfNotifyStr("是");
			}
			
			model.setMobile(PhoneNumNideUtil.hideNum(model.getMobile()));
			model.setCreditUserName(PhoneNumNideUtil.hideName(model.getCreditUserName()));
			model.setCreditCardNum(PhoneNumNideUtil.hideBankCard(model.getCreditCardNum()));
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}
	
	public String exportList(Map<String, Object> paramsMap){
		StringBuilder sb = new StringBuilder("平台,激活产品,激活渠道,注册时间,最后一次打开时间,添加信用卡时间,用户ID,手机号码,持卡人姓名,发卡银行,信用卡卡号,信用卡额度,每月账单日,是否提醒\r\n");
		List<CreditCardUserInfo> list = new ArrayList<CreditCardUserInfo>();
		try {
			list = getList(null, null, new PageResponse(), paramsMap);
		} catch (Exception e) {
			LogService.error(e);
		}
		
		for(CreditCardUserInfo creditCardUserInfo: list){
			String platform = creditCardUserInfo.getPlatform();
			String productName  = creditCardUserInfo.getProdName();
			String channelName = creditCardUserInfo.getChannelName();
			Date registerTime = creditCardUserInfo.getRegiTime();
			Date lastTime = creditCardUserInfo.getLastTime();
			Date createTime = creditCardUserInfo.getCreateTime();
			Long userId = creditCardUserInfo.getUserid();
			String mobile = PhoneNumNideUtil.hideNum(creditCardUserInfo.getMobile());
			String creditUserName = PhoneNumNideUtil.hideName(creditCardUserInfo.getCreditUserName());
			String creditCardBank = creditCardUserInfo.getCreditCardBank();
			String creditCardNum = PhoneNumNideUtil.hideBankCard(creditCardUserInfo.getCreditCardNum());
			Integer creditLimit = creditCardUserInfo.getCreditLimit();
			Integer statementDate = creditCardUserInfo.getStatementDate();
			String ifNotifyStr = creditCardUserInfo.getIfNotify() == 0 ? "否" : "是";
			
			sb.append(platform+ ",")
			  .append(productName + ",")
			  .append(channelName + ",")
			  .append(registerTime + ",")
			  .append(lastTime + ",")
			  .append(createTime + ",")
			  .append(userId + ",")
			  .append(mobile + ",")
			  .append(creditUserName + ",")
			  .append(creditCardBank + ",")
			  .append(creditCardNum + ",")
			  .append(creditLimit + ",")
			  .append(statementDate + ",")
			  .append(ifNotifyStr + "\r\n");
		}
		return sb.toString();
	}
	
	public String getChannelName(List<LoanBackCore> channelList,String id){
		for(LoanBackCore core:channelList){
			if(core.getChannelId().equals(id)){
				return core.getChannel();
			}
		}
		return id;
	}
	
	public String getProdName(List<LoanBackCore> prodList,String id){
		for(LoanBackCore core:prodList){
			if(core.getAppProdId().equals(id)){
				return core.getProdName();
			}
		}
		return id;
	}
	
	public static String getPercent(Long a, Long b) {
		return (float) Math.round((float) a / b * 10000) / 100 + "%";
	}
	
	public static String getPercent(String a, String b) {
		if (StringUtils.isEmpty(a) || StringUtils.isEmpty(b)){
			return "0";
		}
		Long al = Long.parseLong(a);
		Long bl = Long.parseLong(b);

		return (float) Math.round((float) al / bl * 10000) / 100 + "%";
	}
	
}
